insert overwrite table jms_dwd.dwd_tms_shipment_stop_point_dt
select
     shipment_no             --任务号
    ,shipment_name           --任务号名称
    ,network_code            --网点编码
    ,network_name            --网点名称
    ,planned_arrival_time    --计划到达日期+时间
    ,actual_arrival_time     --实际到达时间
    ,planned_departure_time  --计划离开日期+时间
    ,actual_departure_time   --实际发车时间
    ,latest_arrival_time     --最晚到车时间+日期
    ,lock_time               --锁车时间
    ,unlock_time             --锁车时间
    ,is_transfer             --是否中转
    ,pre_network_code        --上一站编码
    ,pre_network_name        --上一站名称
    ,ship_type               --任务类型
    ,dt
from (
    select
         shipment_no                  as shipment_no             --任务号
        ,shipment_name                as shipment_name           --任务号名称
        ,network_code                 as network_code            --网点编码
        ,network_name                 as network_name            --网点名称
        ,min(planned_arrival_time)    as planned_arrival_time    --计划到达日期+时间
        ,min(actual_arrival_time)     as actual_arrival_time     --实际到达时间
        ,min(planned_departure_time)  as planned_departure_time  --计划离开日期+时间
        ,min(actual_departure_time)   as actual_departure_time   --实际发车时间
        ,min(latest_arrival_time)     as latest_arrival_time     --最晚到车时间+日期
        ,min(lock_time)               as lock_time               --锁车时间
        ,min(unlock_time)             as unlock_time             --锁车时间
        ,if(min(sort_num) < min(cnt) and min(sort_num) > 1 ,1,0) as is_transfer        -- 是否中转
        ,max(pre_network_code)        as pre_network_code
        ,max(pre_network_name)        as pre_network_name
        ,'干线' as ship_type
        ,dt
    from (
        select
             a.shipment_no
            ,b.shipment_name
            ,a.network_code
            ,a.network_name
            ,a.planned_arrival_time
            ,a.actual_arrival_time
            ,a.planned_departure_time
            ,a.actual_departure_time
            ,a.latest_arrival_time
            ,a.lock_time
            ,a.unlock_time
            ,a.sort_num
            ,a.pre_network_code
            ,a.pre_network_name
            ,b.cnt
            ,a.dt
        from (
            select
                 a.shipment_no
                ,a.network_code
                ,a.network_name
                ,a.planned_arrival_time
                ,a.actual_arrival_time
                ,a.planned_departure_time
                ,a.actual_departure_time
                ,a.latest_arrival_time
                ,a.lock_time
                ,a.unlock_time
                ,a.sort_num
                ,b.network_code as pre_network_code    --经停网点编码
                ,b.network_name as pre_network_name    --经停网点名称
                ,a.dt
            from (
                select
                     shipment_no
                    ,network_code
                    ,network_name
                    ,planned_arrival_time
                    ,actual_arrival_time
                    ,planned_departure_time
                    ,actual_departure_time
                    ,latest_arrival_time
                    ,lock_time
                    ,unlock_time
                    ,sort_num
                    ,dt
                from jms_ods.yl_tmsnew_tms_shipment_stop  --干线经停表
                where dt = '{{ execution_date | cst_ds }}'
            ) a
            left join (
                select
                     shipment_no
                    ,network_code
                    ,network_name
                    ,planned_arrival_time
                    ,actual_arrival_time
                    ,planned_departure_time
                    ,actual_departure_time
                    ,latest_arrival_time
                    ,lock_time
                    ,unlock_time
                    ,sort_num
                    from jms_ods.yl_tmsnew_tms_shipment_stop  --干线经停表
                 where dt = '{{ execution_date | cst_ds }}'
            ) b on a.shipment_no = b.shipment_no
              and a.sort_num -1 = b.sort_num
        ) a
        left join (
             select *
             from jms_ods.yl_tmsnew_tms_shipment --干线任务表
             where dt = '{{ execution_date | cst_ds }}'
        ) b on a.shipment_no = b.shipment_no
    ) a group by shipment_no
                ,shipment_name
                ,network_code
                ,network_name
                ,dt
) ship where ship.is_transfer = 1
distribute by dt,pmod(hash(rand()),30);

